package com.ruoyi;

import com.alibaba.excel.EasyExcel;

import java.io.*;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * @since 2024/12/11 10:32
 */
public class EasyExcelTest {

    public static void main(String[] args) {
        //  new EasyExcelTest().testneed();
        String dir = "F:/moban/shougong20241211/";  //前端  eastvue/public目录下的  xxx表-填报说明.xlsx 文件
        try {

            WriteExcelByPoi(dir+"董监高履职信息表-填报说明.xlsx","董监高履职信息表-填报说明",5,6);
            WriteExcelByPoi(dir+"分支机构信息表-填报说明.xlsx","分支机构信息表-填报说明",6,7);
            WriteExcelByPoi(dir+"关联方信息表-填报说明.xlsx","关联方信息表-填报说明",6,7);
            WriteExcelByPoi(dir+"关联交易汇总表-填报说明.xlsx","关联交易汇总表-填报说明",6,7);
            WriteExcelByPoi(dir+"机构股权信息表-填报说明.xlsx","机构股权信息表-填报说明",6,7);
            WriteExcelByPoi(dir+"委托投资情况表-填报说明.xlsx","委托投资情况表-填报说明",6,7);
            WriteExcelByPoi(dir+"险种定义表-填报说明.xlsx","填报说明",5,6);
            WriteExcelByPoi(dir+"员工信息表-填报说明.xlsx","员工信息表-填报说明",6,7);
            WriteExcelByPoi(dir+"重大关联交易表-填报说明.xlsx","重大关联交易表-填报说明",6,7);
            WriteExcelByPoi(dir+"自主投资交易流水表-填报说明.xlsx","自主投资交易流水表-填报说明",6,7);
            WriteExcelByPoi(dir+"自主投资账户持仓明细表-填报说明.xlsx","自主投资账户持仓明细表-填报说明",6,7);
            WriteExcelByPoi(dir+"自主投资账户信息汇总表-填报说明.xlsx","自主投资账户信息汇总表-填报说明",6,7);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }



    //idx: 示例数据在第几行
    //nullidex:  从第几行开始是空的，需要创建行，创建单元格，设置单元格可编辑
    //使用POI设置导出的EXCEL锁定指定的单元格
    //https://www.cnblogs.com/huanghongbo/p/4955772.html
    public static void WriteExcelByPoi(String path,String sheetname,int idx,int nullidex) throws Exception,
            InvalidFormatException {
        //String path = "d:/code/44.xlsx";  //44.xlsx  换成任意一个文件前端public目录中的 xxx-填报说明文件
        System.out.println(path);
        try {
            InputStream in = new FileInputStream(new File(path));

            Workbook workbook = new XSSFWorkbook(in);
            int sheetIndex = workbook.getSheetIndex(sheetname);
           // int sheetidx = (org.apache.poi.ss.usermodel.Sheet) sheetIndex;
            org.apache.poi.ss.usermodel.Sheet sheet = (org.apache.poi.ss.usermodel.Sheet)workbook.getSheetAt(sheetIndex);
            sheet.protectSheet(""); //设置表单保护密码


            org.apache.poi.ss.usermodel.Row row = null;
            org.apache.poi.ss.usermodel.Cell cell = null;

            String cellValue = "132700002800";
            XSSFCellStyle alterableStyle = (XSSFCellStyle)workbook.createCellStyle(); //获取当前单元格的样式对象
            alterableStyle.setLocked(true);    //设定此单元格为锁定状态
            XSSFCellStyle nolockedStyle = (XSSFCellStyle)workbook.createCellStyle(); //获取当前单元格的样式对象
            nolockedStyle.setLocked(false);    //设定此单元格为非锁定状态

            String value = "非锁定";

            row = sheet.getRow(idx);
           int  cellMaxNum=row.getLastCellNum();
//            for (int i = 0; i < cellMaxNum; i++) {
//                cell = row.getCell(i);
//                cell.setCellStyle(nolockedStyle);
////                System.out.println("dddd");
//            }


            for (int i = idx; i < 10000; i++) {
                row = sheet.createRow(i);
                for (int j = 0; j < cellMaxNum; j++) {
                    cell = row.createCell(j);
                  //  cell.setCellValue(value);
                    cell.setCellStyle(nolockedStyle);
                }

//                System.out.println(" i =" + i);
//                row = sheet.createRow(i);
//                cell = row.createCell(0);
//              //  cell.setCellValue(cellValue);
//                cell.setCellStyle(alterableStyle);
//
//                cell = row.createCell(1);
//                cell.setCellValue(value);
//                cell.setCellStyle(nolockedStyle);
//
//                cell = row.createCell(2);
//                cell.setCellValue(value);
//                cell.setCellStyle(nolockedStyle);
//
//                cell = row.createCell(3);
//                cell.setCellValue(value);
//                cell.setCellStyle(nolockedStyle);
            }

            in.close();

            FileOutputStream out = null;
            try {
                out = new FileOutputStream(path);
                workbook.write(out);
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

